/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/*
 * LatLonJsToMySQL.java
 *
 * Created on 18-mar-2010, 0.49.47
 */
package freimapgsoc;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Component;
import java.awt.Container;
import java.awt.FlowLayout;
import java.awt.Frame;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.beans.PropertyChangeEvent;
import java.beans.PropertyChangeListener;
import java.io.BufferedReader;
import java.io.File;
import java.io.InputStreamReader;
import java.net.URL;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.StringTokenizer;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JButton;
import javax.swing.JDialog;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JOptionPane;

/**
 *
 * @author Stefano
 */
public class LatLonJsToMySQL extends javax.swing.JFrame {

    /** Creates new form LatLonJsToMySQL */
    public LatLonJsToMySQL() {
        initComponents();
    }

    /**
	 * This method is called from within the constructor to
	 * initialize the form.
	 * WARNING: Do NOT modify this code. The content of this method is
	 * always regenerated by the Form Editor.
	 * @return 
	 */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        confirmPane = new JOptionPane("If you continue your current data in the Database will be destroyed. \n Are you sure that you want to continue?.\n", JOptionPane.QUESTION_MESSAGE, JOptionPane.YES_NO_OPTION);
        ;
        scrollPane = new javax.swing.JScrollPane();
        errorsLog = new javax.swing.JTextArea();
        jLabel1 = new javax.swing.JLabel();
        jLabel2 = new javax.swing.JLabel();
        passwordText = new javax.swing.JPasswordField();
        userText = new javax.swing.JTextField();
        jLabel3 = new javax.swing.JLabel();
        jLabel5 = new javax.swing.JLabel();
        hostText = new javax.swing.JTextField();
        jLabel4 = new javax.swing.JLabel();
        cancelButton = new javax.swing.JButton();
        copyButton = new javax.swing.JButton();
        logLabel = new javax.swing.JLabel();
        checkconnButton = new javax.swing.JButton();
        pathText = new javax.swing.JTextField();
        chooseButton = new javax.swing.JButton();
        jLabel7 = new javax.swing.JLabel();
        showLog = new javax.swing.JButton();
        jLabel6 = new javax.swing.JLabel();
        portText = new javax.swing.JTextField();
        jButton1 = new javax.swing.JButton();
        dbText = new javax.swing.JComboBox();
        newDb = new javax.swing.JCheckBox();
        newDbText = new javax.swing.JTextField();

        confirmPane.setMessage("If you continue your current data in the Database will be destroyed. \n Are you sure that you want to continue?.\n");
        confirmPane.setFont(new java.awt.Font("Lucida Grande", 1, 10));
        confirmPane.setName("confirmPane"); // NOI18N
        confirmPane.addPropertyChangeListener(new java.beans.PropertyChangeListener() {
            public void propertyChange(java.beans.PropertyChangeEvent evt) {
                confirmPanePropertyChange(evt);
            }
        });

        scrollPane.setName("scrollPane"); // NOI18N
        scrollPane.setSize(new java.awt.Dimension(400, 300));

        errorsLog.setColumns(20);
        errorsLog.setEditable(false);
        errorsLog.setFont(new java.awt.Font("Lucida Grande", 0, 10));
        errorsLog.setRows(8);
        errorsLog.setTabSize(10);
        errorsLog.setName("errorsLog"); // NOI18N
        errorsLog.setPreferredSize(new java.awt.Dimension(400, 300));
        errorsLog.setSize(new java.awt.Dimension(300, 400));
        scrollPane.setViewportView(errorsLog);

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        setTitle("Js to MySql Utility");

        jLabel1.setFont(new java.awt.Font("Lucida Grande", 0, 10)); // NOI18N
        jLabel1.setText("Source File:");
        jLabel1.setName("jLabel1"); // NOI18N

        jLabel2.setFont(new java.awt.Font("Lucida Grande", 0, 10));
        jLabel2.setText("Password:");
        jLabel2.setName("jLabel2"); // NOI18N

        passwordText.setFont(new java.awt.Font("Lucida Grande", 0, 12));
        passwordText.setName("passwordText"); // NOI18N

        userText.setFont(new java.awt.Font("Lucida Grande", 0, 12));
        userText.setName("userText"); // NOI18N

        jLabel3.setFont(new java.awt.Font("Lucida Grande", 0, 10));
        jLabel3.setText("UserName:");
        jLabel3.setName("jLabel3"); // NOI18N

        jLabel5.setFont(new java.awt.Font("Lucida Grande", 0, 10));
        jLabel5.setText("Host:");
        jLabel5.setName("jLabel5"); // NOI18N

        hostText.setFont(new java.awt.Font("Lucida Grande", 0, 12)); // NOI18N
        hostText.setName("hostText"); // NOI18N

        jLabel4.setFont(new java.awt.Font("Lucida Grande", 0, 10));
        jLabel4.setText("Avaible Database:");
        jLabel4.setName("jLabel4"); // NOI18N

        cancelButton.setFont(new java.awt.Font("Lucida Grande", 0, 12));
        cancelButton.setText("Cancel");
        cancelButton.setName("cancelButton"); // NOI18N
        cancelButton.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                cancelButtonActionPerformed(evt);
            }
        });

        copyButton.setFont(new java.awt.Font("Lucida Grande", 0, 12)); // NOI18N
        copyButton.setText("Copy All");
        copyButton.setName("copyButton"); // NOI18N
        copyButton.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                copyButtonActionPerformed(evt);
            }
        });

        logLabel.setFont(new java.awt.Font("Lucida Grande", 0, 10));
        logLabel.setText(" ");
        logLabel.setName("logLabel"); // NOI18N

        checkconnButton.setFont(new java.awt.Font("Lucida Grande", 0, 12)); // NOI18N
        checkconnButton.setText("Load Avaible Database");
        checkconnButton.setName("checkconnButton"); // NOI18N
        checkconnButton.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                checkconnButtonActionPerformed(evt);
            }
        });

        pathText.setEditable(false);
        pathText.setFont(new java.awt.Font("Lucida Grande", 0, 12)); // NOI18N
        pathText.setToolTipText("Js File Path");
        pathText.setName("pathText"); // NOI18N

        chooseButton.setFont(new java.awt.Font("Lucida Grande", 0, 12));
        chooseButton.setText("Choose File");
        chooseButton.setName("chooseButton"); // NOI18N
        chooseButton.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                chooseButtonActionPerformed(evt);
            }
        });

        jLabel7.setText("With this utility you can add your LatLon.js data into a mysql Database.");
        jLabel7.setName("jLabel7"); // NOI18N

        showLog.setFont(new java.awt.Font("Lucida Grande", 0, 12));
        showLog.setText("Show Log");
        showLog.setName("showLog"); // NOI18N
        showLog.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                showLogActionPerformed(evt);
            }
        });

        jLabel6.setText(":");
        jLabel6.setName("jLabel6"); // NOI18N

        portText.setFont(new java.awt.Font("Lucida Grande", 0, 12));
        portText.setText("3306");
        portText.setName("portText"); // NOI18N

        jButton1.setFont(new java.awt.Font("Lucida Grande", 0, 12));
        jButton1.setText("Example");
        jButton1.setName("jButton1"); // NOI18N
        jButton1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton1ActionPerformed(evt);
            }
        });

        dbText.setFont(new java.awt.Font("Lucida Grande", 0, 12)); // NOI18N
        dbText.setName("dbText"); // NOI18N

        newDb.setFont(new java.awt.Font("Lucida Grande", 0, 10));
        newDb.setText("I want to create a new Database:");
        newDb.setName("newDb"); // NOI18N
        newDb.addItemListener(new java.awt.event.ItemListener() {
            public void itemStateChanged(java.awt.event.ItemEvent evt) {
                newDbItemStateChanged(evt);
            }
        });

        newDbText.setFont(new java.awt.Font("Lucida Grande", 0, 12));
        newDbText.setEnabled(false);
        newDbText.setName("newDbText"); // NOI18N

        org.jdesktop.layout.GroupLayout layout = new org.jdesktop.layout.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
            .add(layout.createSequentialGroup()
                .add(41, 41, 41)
                .add(jLabel7)
                .addContainerGap(54, Short.MAX_VALUE))
            .add(layout.createSequentialGroup()
                .add(106, 106, 106)
                .add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
                    .add(layout.createSequentialGroup()
                        .add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING, false)
                            .add(layout.createSequentialGroup()
                                .add(81, 81, 81)
                                .add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.TRAILING, false)
                                    .add(org.jdesktop.layout.GroupLayout.LEADING, jButton1, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                    .add(org.jdesktop.layout.GroupLayout.LEADING, checkconnButton)))
                            .add(logLabel, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, 315, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)
                            .add(layout.createSequentialGroup()
                                .add(newDb)
                                .addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED)
                                .add(newDbText)))
                        .addContainerGap())
                    .add(org.jdesktop.layout.GroupLayout.TRAILING, layout.createSequentialGroup()
                        .add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.TRAILING)
                            .add(layout.createSequentialGroup()
                                .add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
                                    .add(org.jdesktop.layout.GroupLayout.TRAILING, jLabel5, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, 94, Short.MAX_VALUE)
                                    .add(org.jdesktop.layout.GroupLayout.TRAILING, jLabel3, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, 94, Short.MAX_VALUE)
                                    .add(org.jdesktop.layout.GroupLayout.TRAILING, jLabel2, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, 94, Short.MAX_VALUE)
                                    .add(org.jdesktop.layout.GroupLayout.TRAILING, jLabel4, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, 94, Short.MAX_VALUE))
                                .addPreferredGap(org.jdesktop.layout.LayoutStyle.UNRELATED)
                                .add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING, false)
                                    .add(dbText, 0, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                    .add(org.jdesktop.layout.GroupLayout.TRAILING, userText, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, 160, Short.MAX_VALUE)
                                    .add(org.jdesktop.layout.GroupLayout.TRAILING, passwordText)
                                    .add(hostText))
                                .add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
                                    .add(layout.createSequentialGroup()
                                        .add(5, 5, 5)
                                        .add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING, false)
                                            .add(copyButton, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                            .add(showLog, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, 97, Short.MAX_VALUE)
                                            .add(cancelButton)))
                                    .add(layout.createSequentialGroup()
                                        .add(jLabel6)
                                        .addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED)
                                        .add(portText, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, 53, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE))))
                            .add(layout.createSequentialGroup()
                                .add(jLabel1, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED)
                                .add(pathText, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, 191, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)
                                .add(5, 5, 5)
                                .add(chooseButton)))
                        .add(77, 77, 77))))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
            .add(layout.createSequentialGroup()
                .add(34, 34, 34)
                .add(jLabel7)
                .add(18, 18, 18)
                .add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.BASELINE)
                    .add(jLabel1)
                    .add(chooseButton)
                    .add(pathText, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE))
                .add(4, 4, 4)
                .add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.TRAILING)
                    .add(layout.createSequentialGroup()
                        .add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.BASELINE)
                            .add(hostText, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)
                            .add(jLabel5)
                            .add(jLabel6)
                            .add(portText, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE))
                        .addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED)
                        .add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.BASELINE)
                            .add(jLabel3)
                            .add(userText, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE))
                        .addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED)
                        .add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.BASELINE)
                            .add(jLabel2)
                            .add(passwordText, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE))
                        .addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED)
                        .add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.BASELINE)
                            .add(jLabel4)
                            .add(dbText, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)))
                    .add(layout.createSequentialGroup()
                        .add(copyButton)
                        .addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED)
                        .add(showLog)
                        .addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED)
                        .add(cancelButton)))
                .addPreferredGap(org.jdesktop.layout.LayoutStyle.UNRELATED)
                .add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.BASELINE)
                    .add(newDb)
                    .add(newDbText, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE))
                .add(24, 24, 24)
                .add(logLabel, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, 24, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(org.jdesktop.layout.LayoutStyle.UNRELATED)
                .add(checkconnButton)
                .addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED, 11, Short.MAX_VALUE)
                .add(jButton1)
                .addContainerGap())
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void cancelButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_cancelButtonActionPerformed
        new Start().setVisible(true);
        this.dispose();
}//GEN-LAST:event_cancelButtonActionPerformed

    private void copyButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_copyButtonActionPerformed
        host = hostText.getText();
        port = portText.getText();
        username = userText.getText();
        password = passwordText.getText();
        if (newDb.isSelected()) {
            database = newDbText.getText();
        } else {
            database = dbText.getSelectedItem().toString();
        }
        Component source = (Component) evt.getSource();
        int i = confirmPane.showConfirmDialog(source, "Are you sure?", "Database Replace", JOptionPane.YES_NO_OPTION);
        if (i == 0) {
            try {
                createTables();
                parseFile(path);
            } catch (ClassNotFoundException ex) {
                logLabel.setText(ex.getMessage());
                Logger.getLogger(LatLonJsToMySQL.class.getName()).log(Level.SEVERE, null, ex);
            } catch (SQLException ex) {
                logLabel.setText(ex.getMessage());
                Logger.getLogger(LatLonJsToMySQL.class.getName()).log(Level.SEVERE, null, ex);
            } catch (Exception ex) {
                ex.getMessage();
            }
        } else {
            confirmPane.setVisible(false);
        }
}//GEN-LAST:event_copyButtonActionPerformed

    private void checkconnButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_checkconnButtonActionPerformed
        try {
            host = hostText.getText();
            port = portText.getText();
            username = userText.getText();
            password = passwordText.getText();
            if (host.length() < 2) {
                if (port.length() == 0) {
                    port = "3306";
                }
                logLabel.setForeground(Color.red);
                logLabel.setText("Add host address and port number!");
            } else {

                logLabel.setText("Getting connection...");
                Thread.sleep(1000);
                Class.forName("com.mysql.jdbc.Driver");
                c = (Connection) DriverManager.getConnection("jdbc:mysql://" + host + ":" + port, username, password);
                if (!c.isClosed()) {
                    stmt = (Statement) c.createStatement();
                    rss = stmt.executeQuery("SHOW DATABASES");
                    int i = 0;
                    while (rss.next()) {
                        dbText.insertItemAt(rss.getString("Database"), i);
                        i++;
                        logLabel.setForeground(Color.green);
                        logLabel.setText("Connected!");
                        dbText.setSelectedIndex(0);
                    }
                }
            }
        } catch (Exception ex) {
            logLabel.setForeground(Color.red);
            logLabel.setText(ex.getMessage());
        }

    }//GEN-LAST:event_checkconnButtonActionPerformed

    private void createTables() throws ClassNotFoundException, SQLException {
        try {
            logLabel.setForeground(Color.black);
            logLabel.setText("Creating Table in " + database + " database...");
            Class.forName("com.mysql.jdbc.Driver");
            errors.put("DATABASE:", "Getting Connection for CreateTables...\n");
            logLabel.setText("Getting Connection for CreateTables...");

            String queryDel = "DROP SCHEMA IF EXISTS " + database + ";";

            String queryCreate = "CREATE SCHEMA IF NOT EXISTS " + database + ";";

            String queryUse = "use " + database + "; ";

            String query = "CREATE TABLE nodes (lon float DEFAULT '12.5535', "
                    + "lat float DEFAULT '41.8638', "
                    + "ip varchar(32) NOT NULL, "
                    + "name varchar(32) NOT NULL, "
                    + "isGateway binary(1) NOT NULL, "
                    + "gatewayIp varchar(32) NOT NULL, "
                    + "uptime varchar(10) DEFAULT NULL,"
                    + "interfaces varchar(50) DEFAULT NULL, "
                    + "PRIMARY KEY (name),KEY ip (ip)"
                    + ") ENGINE=InnoDB DEFAULT CHARSET=latin1; ";

            String query2 = "CREATE TABLE flows (RECNUM bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT, "
                    + "PROBE varchar(16) NOT NULL DEFAULT '',TIME_RECEIVED datetime NOT NULL DEFAULT '0000-00-00 00:00:00',"
                    + "FLOW_VERSION tinyint(4) NOT NULL DEFAULT '0',"
                    + "SEQUENCE bigint(20) unsigned zerofill NOT NULL DEFAULT '00000000000000000000',"
                    + "SOURCE_IP varchar(16) NOT NULL DEFAULT '',"
                    + "SOURCE_PORT int(11) NOT NULL DEFAULT '0',"
                    + "DEST_IP varchar(16) NOT NULL DEFAULT '',"
                    + "DEST_PORT int(11) NOT NULL DEFAULT '0',"
                    + "NEXT_HOP varchar(16) NOT NULL DEFAULT '',"
                    + "PROTOCOL int(11) NOT NULL DEFAULT '0',"
                    + "TCP_FLAGS int(11) NOT NULL DEFAULT '0',"
                    + "TOS int(11) NOT NULL DEFAULT '0',"
                    + "BYTES bigint(20) unsigned NOT NULL DEFAULT '0',"
                    + "PACKETS bigint(20) unsigned NOT NULL DEFAULT '0',"
                    + "FLOW_BEGIN datetime NOT NULL DEFAULT '0000-00-00 00:00:00',"
                    + "FLOW_END datetime NOT NULL DEFAULT '0000-00-00 00:00:00',"
                    + "SOURCE_AS int(11) NOT NULL DEFAULT '0',"
                    + "DEST_AS int(11) NOT NULL DEFAULT '0',"
                    + "SOURCE_MASK smallint(6) NOT NULL DEFAULT '0',"
                    + "DEST_MASK smallint(6) NOT NULL DEFAULT '0',"
                    + "PRIMARY KEY (RECNUM),KEY k_begin (FLOW_BEGIN),"
                    + "KEY k_end (FLOW_END),KEY k_time (TIME_RECEIVED),"
                    + "KEY k_src (SOURCE_IP),KEY k_dst (DEST_IP)"
                    + ") ENGINE=MyISAM DEFAULT CHARSET=latin1;";

            String query3 = "CREATE TABLE links (clock timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',"
                    + "src varchar(32) NOT NULL DEFAULT '',"
                    + "dest varchar(32) NOT NULL DEFAULT '',"
                    + "lq float DEFAULT NULL,"
                    + "nlq float DEFAULT NULL,"
                    + "etx float DEFAULT NULL,"
                    + "PRIMARY KEY (clock,src,dest),"
                    + "KEY src (src),"
                    + "KEY dest (dest),"
                    + "CONSTRAINT links_ibfk_2 FOREIGN KEY (dest) REFERENCES nodes (ip),"
                    + "CONSTRAINT links_ibfk_1 FOREIGN KEY (src) REFERENCES nodes (ip)"
                    + ") ENGINE=InnoDB DEFAULT CHARSET=latin1; ";

            String query4 = "CREATE TABLE layer ("
                    + "id int(11) NOT NULL AUTO_INCREMENT,"
                    + "srcnode varchar(32) DEFAULT NULL,"
                    + "clock varchar(10) NULL DEFAULT NULL,"
                    + "destnode varchar(32) DEFAULT NULL,"
                    + "PRIMARY KEY (id),"
                    + "KEY srcnode (srcnode),"
                    + "KEY destnode (destnode),"
                    + "CONSTRAINT layer_ibfk_2 FOREIGN KEY (destnode) REFERENCES links (dest),"
                    + "CONSTRAINT layer_ibfk_1 FOREIGN KEY (srcnode) REFERENCES links (src)"
                    + ") ENGINE=InnoDB DEFAULT CHARSET=latin1;";

            String query5 = "CREATE TABLE interfaces (mainIp varchar(32) NOT NULL DEFAULT '',"
                    + "IntIp varchar(32) DEFAULT NULL,"
                    + "KEY mainIp (mainIp)"
                    + ") ENGINE=InnoDB DEFAULT CHARSET=latin1";

            Connection c2 = (Connection) DriverManager.getConnection("jdbc:mysql://" + host + ":" + port, username, password);
            Statement stmt2 = (Statement) c2.createStatement();
            errors.put("DATABASE:", "Connected to the database...\n");
            logLabel.setText("Execute Queries...");

            Thread.sleep(3000);
            System.out.println(queryDel);

            System.out.println(queryCreate);
            System.out.println(queryUse);
            System.out.println(query);
            System.out.println(query2);
            System.out.println(query3);
            System.out.println(query4);
            System.out.println(query5);


            stmt2.executeUpdate(queryDel);
            errors.put("DATABASE_QUERY:", queryDel + "\n");

            stmt2.executeUpdate(queryCreate);
            errors.put("DATABASE_QUERY:", queryCreate + "\n");

            stmt2.executeQuery(queryUse);
            errors.put("DATABASE_QUERY:", queryUse + "\n");

            Thread.sleep(1000);
            stmt2.executeUpdate(query);
            errors.put("DATABASE_QUERY:", query + "\n");
            Thread.sleep(1000);

            stmt2.executeUpdate(query2);
            errors.put("DATABASE_QUERY:", query2 + "\n");
            Thread.sleep(1000);

            stmt2.executeUpdate(query3);
            errors.put("DATABASE_QUERY:", query3 + "\n");
            Thread.sleep(1000);

            stmt2.executeUpdate(query4);
            errors.put("DATABASE:", query4 + "\n");
            Thread.sleep(1000);

            stmt2.executeUpdate(query5);
            errors.put("DATABASE:", query5 + "\n");
            Thread.sleep(1000);

            errors.put("DATABASE:", "Now Adding nodes and Links....! This will take a while...\n");
            logLabel.setText("Now Adding nodes and Links....! This will take a while..");
            Thread.sleep(1500);

        } catch (Exception e) {
            System.out.println(e.getMessage());
        }

    }


    private void showLogActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_showLogActionPerformed
        scrollPane.setSize(398, 298);
        errorsLog.append(errors.values().toString());
        frame = new Frame("Activity Log");
        frame.add(scrollPane);
        frame.setLayout(new FlowLayout());
        frame.setSize(400, 300);
        frame.setVisible(true);
        frame.addWindowListener(new WindowAdapter() {

            public void windowClosing(WindowEvent e) {
                frame.setVisible(false);


            }
        });
    }//GEN-LAST:event_showLogActionPerformed

    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
        pathText.setEditable(true);
        pathText.setText("/Users/Stefano/Desktop/FreimapSte/Freimap/hg/src/data/latlon.js");
        userText.setText("root");
        passwordText.setText("CiscoSte5785");
        hostText.setText("127.0.0.1");
    }//GEN-LAST:event_jButton1ActionPerformed

    private void confirmPanePropertyChange(java.beans.PropertyChangeEvent evt) {//GEN-FIRST:event_confirmPanePropertyChange
        System.out.println("PopUp3");
        String prop = evt.getPropertyName();


        if (evt.getSource().equals(confirmPane) && prop.equals(JOptionPane.YES_OPTION)) {
            try {
                logLabel.setText("Getting connection...");
                Thread.sleep(1000);
                Class.forName("com.mysql.jdbc.Driver");
                c = (Connection) DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database, username, password);


                if (!c.isClosed()) {
                    logLabel.setForeground(Color.green);
                    logLabel.setText("Connected!");
                    createTables();
                    parseFile(
                            pathText.getText());
                    Thread.sleep(3000);


                }
            } catch (Exception ex) {
                logLabel.setText(ex.getMessage());
                Logger.getLogger(LatLonJsToMySQL.class.getName()).log(Level.SEVERE, null, ex);
            }


        } else {
            optionPane.setEnabled(false);


        }

// TODO add your handling code here:
    }//GEN-LAST:event_confirmPanePropertyChange

    private void newDbItemStateChanged(java.awt.event.ItemEvent evt) {//GEN-FIRST:event_newDbItemStateChanged
        if (newDb.isSelected()) {
            newDbText.setEnabled(true);
            dbText.setEnabled(false);


        } else if (!newDb.isSelected()) {
            newDbText.setEnabled(false);
            dbText.setEnabled(true);


        }
    }//GEN-LAST:event_newDbItemStateChanged

    private void chooseButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_chooseButtonActionPerformed
        JFileChooser fcjs = new JFileChooser();
        fcjs.addChoosableFileFilter(new jsFileFilter());
        fcjs.setAcceptAllFileFilterUsed(false);
        fcjs.setFileFilter(new jsFileFilter());


        int returnVal = fcjs.showOpenDialog(fcjs);


        if (returnVal == JFileChooser.APPROVE_OPTION) {
            File file = fcjs.getSelectedFile();
            System.out.println("FILE JS OPENED");
            System.out.println("Opening: " + file.getName() + ".\n");
            pathText.setText(file.getPath());
            path = "file://" + pathText.getText();


        } else if (returnVal == JFileChooser.CANCEL_OPTION) {
            System.out.println("Open command cancelled by user." + "\n");


        }
}//GEN-LAST:event_chooseButtonActionPerformed

     public void parseFile(String path) throws SQLException, InterruptedException {
        System.out.println("Now Parse Node and Links");
        errors.put("PARSE FILE:", "Now Parse Node and Links...\n");
        parseNode(path);
        Thread.sleep(3000);
        parseLink(path);
        Thread.sleep(3000);
        parseInterfaces(path);
        logLabel.setText("OK, ALL DONE! SEE LOG FOR ERRORS");
        System.out.println("OK, ALL DONE! SEE LOG FOR ERRORS!");
        errors.put("JSTOMYSQL", "FINISHED!!\n");
    }

    public void parseInterfaces(String path) {
        logLabel.setText("Now control if nodes has more than one interface...");
        try {
            BufferedReader in = new BufferedReader(new InputStreamReader(new URL(path).openStream()));
            //PARSE MID
            while (true) {
                line = in.readLine();
                //System.out.println("Line: " + line);
                if (line == null) {
                    break;//if there aren't string in a file
                }
                if ((line.length() > 3) && (line.substring(0, 3).equals("Mid"))) {
                    StringTokenizer stnode = new StringTokenizer(line.substring(4, line.length() - 2), ",", false);
                    String nodeip = stnode.nextToken();
                    String nodeip2 = stnode.nextToken();
                    System.out.println("node " + nodeip + " has also " + nodeip2 + " interfaces");
                    errors.put(nodeip, "This node has also " + nodeip2 + " interfaces");
                    nodeip = stripQuotes(nodeip);
                    nodeip2 = stripQuotes(nodeip2);

                    c = (Connection) DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database, username, password);
                    stmt = (Statement) c.createStatement();
                    String query = "INSERT INTO interfaces VALUES (" + "\"" + nodeip + "\"" + "," + "\"" + nodeip2 + "\"" + ");";
                    System.out.println(query);
                    stmt.executeUpdate(query);
                    System.out.println(query);
                    errors.put("DATABASE_QUERY:", query + "\n");
                    c.close();
                }

            }


        } catch (Exception ex) {
            Logger.getLogger(LatLonJsDataSource.class.getName()).log(Level.SEVERE, null, ex);

        }


    }

    public void parseNode(String path) throws SQLException {
        try {
            BufferedReader in = new BufferedReader(new InputStreamReader(new URL(path).openStream()));
            while (true) {
                line = in.readLine();
                //System.out.println("Line: " + line);


                if (line == null) {
                    break;//if there aren't string in a file


                } //if substring is Node then add a node
                if ((line.length() > 4) && (line.substring(0, 4).equals("Node"))) {

                    //PARSE NODE
                    StringTokenizer st = new StringTokenizer(line.substring(5, line.length() - 2), ",", false);
                    String ip = st.nextToken();
                    double lat = Double.parseDouble(st.nextToken());
                    double lon = Double.parseDouble(st.nextToken());
                    int isgateway = Integer.parseInt(st.nextToken());
                    String gatewayip = st.nextToken();
                    String name = st.nextToken();
                    ip = stripQuotes(ip); //strip single quotes
                    name = stripQuotes(name);
                    gatewayip = stripQuotes(gatewayip);

                    System.out.println("IP Address: " + ip);
                    System.out.println("lat:" + lat);
                    System.out.println("lon:" + lon);
                    System.out.println("isgateway:" + isgateway);
                    System.out.println("gatewayip:" + gatewayip);
                    System.out.println("name:" + name);

                    // Use ip or coordinates as fqid if tooltip is missing
                    if (ip == null) { //i not need this but....! :-D
                        ip = null;
                    }

                    if (name == null) {
                        errors.put(name, "This node has no name!\n");
                        if (ip == null) {
                            errors.put(name, "This node has also no ip! I use (lat,lon) as name!\n");
                            name = lat + "," + lon;
                        } else {
                            errors.put(name, "I use IP Address as name!\n");
                            name = ip;

                        }
                    }
                    if (ip == null) { //we need at least one identifier
                        ip = name;
                    }

                    c = (Connection) DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database, username, password);
                    stmt = (Statement) c.createStatement();
                    String query = "INSERT INTO nodes VALUES (" + lat + "," + lon + "," + "\"" + ip + "\"" + "," + "\"" + name + "\"" + "," + isgateway + "," + "\"" + gatewayip + "\"" + "," + "\"" + "00:00:00" + "\"" + ", NULL);";
                    System.out.println(query + "\n");
                    stmt.executeUpdate(query);
                    errors.put("DATABASE_QUERY:", query + "\n");
                    c.close();


                }
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());


        }
    }

    public void parseLink(String path) throws SQLException {
        try {
            BufferedReader in = new BufferedReader(new InputStreamReader(new URL(path).openStream()));


            while (true) {
                line = in.readLine();
                if (line == null) {
                    break;//if there aren't string in a file
                }
                if ((line.length() > 5) && (line.substring(0, 4).equals("Link"))) {
                    //PARSE LINK
                    StringTokenizer stlink = new StringTokenizer(line.substring(5, line.length() - 2), ",", false);
                    String srclink = stlink.nextToken();
                    String destlink = stlink.nextToken();

                    double lqlink = Double.parseDouble(stlink.nextToken());
                    double nlqlink = Double.parseDouble(stlink.nextToken());
                    double etxlink = Double.parseDouble(stlink.nextToken());

                    srclink = stripQuotes(srclink);
                    destlink = stripQuotes(destlink);

                    c = (Connection) DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database, username, password);
                    stmt = (Statement) c.createStatement();
                    String query = "INSERT INTO links VALUES (" + "\"" + "0000-00-00 00:00:00" + "\"" + "," + "\"" + srclink + "\"" + "," + "\"" + destlink + "\"" + "," + (float) lqlink + "," + (float) nlqlink + "," + (float) etxlink + ");";
                    stmt.executeUpdate(query);
                    errors.put("DATABASE_QUERY:", query + "\n");

                }
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());


        } finally {
            c.close();


        }
    }

    public String addSlashes(String str) {
        if (str == null) {
            return "";


        }
        StringBuffer s = new StringBuffer((String) str);


        for (int i = 0; i
                < s.length(); i++) {
            if (s.charAt(i) == '\"') {
                s.insert(i++, '\\');


            }
        }
        return s.toString();


    }

    private String stripQuotes(String str) {
        if (str.length() <= 2) {
            return null;

        }
        return str.substring(1, str.length() - 1);

    }

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        java.awt.EventQueue.invokeLater(new Runnable() {

            public void run() {
                new LatLonJsToMySQL().setVisible(true);



            }
        });





    }
    Frame frame = null;
    Connection c = null;
    ResultSet rss = null;
    Statement stmt = null;
    String line = null;
    HashMap<String, String> errors = new HashMap<String, String>();
    String host;
    String username;
    String password;
    String database;
    String port;
    JOptionPane optionPane = null;
    String path;
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton cancelButton;
    private javax.swing.JButton checkconnButton;
    private javax.swing.JButton chooseButton;
    private javax.swing.JOptionPane confirmPane;
    private javax.swing.JButton copyButton;
    private javax.swing.JComboBox dbText;
    private javax.swing.JTextArea errorsLog;
    private javax.swing.JTextField hostText;
    private javax.swing.JButton jButton1;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JLabel jLabel6;
    private javax.swing.JLabel jLabel7;
    private javax.swing.JLabel logLabel;
    private javax.swing.JCheckBox newDb;
    private javax.swing.JTextField newDbText;
    private javax.swing.JPasswordField passwordText;
    private javax.swing.JTextField pathText;
    private javax.swing.JTextField portText;
    private javax.swing.JScrollPane scrollPane;
    private javax.swing.JButton showLog;
    private javax.swing.JTextField userText;
    // End of variables declaration//GEN-END:variables
}
